SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SaveCOI]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SaveCOI]
GO

  
/***************************************************************************  
* Name:   SaveCOI  
* Purpose:  Insert/Update a COI  
*  
* PARAMETERS  
* Name     Description       
* -------------    -----------------------------------------------------------  
* @id     unique id (0 for new records to be inserted)  
* @admPersonId    user's person id  
* @fwkDomainUserId   user's domain id  
* @lastNotified    last date on which the request to fill up the COI is raised  
* @lastModified    last date on which the COI was modified  
* @dateSubmitted   Date on which the COI is submitted  
* @dateReviewed    Date on which the COI is reviewed  
* @hasOutsideActivities   indicates whether or not the link to add Outside Activities should be displayed  
* @hasEquityInterests    indicates whether or not the link to add Equity Interests should be displayed  
* @hasFamilyAssociations  indicates whether or not the link to add Family Associatio should be displayed  
* @hasRoyalties		  indicates whether or not the link to add Royalties should be displayed
* @aggregateEquity    Aggregate Equity  
* @policyViolation    indicates whether or not there is any violation of the Policy Rules  
* @otherActivity    indicates whether or not the user has an association to a protocol or an agreement within Insight  
* @status     status of the COI  
* @documentId     id of the document (pdf document of the COI - for submitted and reviewed COIs)  
* @dateToUpdate    1 - Update Submitted Date, 2 - Update Reveiwed Date, 3 - Update Notified Date  
***************************************************************************/  
CREATE PROCEDURE [dbo].[SaveCOI] (  
  
	@id    INT,  
	@admPersonId  INT,  
	@fwkDomainUserId INT,  
	@lastNotified  DATETIME OUT,  
	@dateSubmitted  DATETIME OUT,  
	@dateReviewed  DATETIME OUT,  
	@hasOutsideActivities BIT,  
	@hasEquityInterests BIT,  
	@hasFamilyAssociations BIT,
	@hasRoyalties BIT,  
	@aggregateEquity INT,  
	@policyViolation BIT,  
	@otherActivity BIT ,  
	@status VARCHAR(50),  
	@documentId INT = null,  
	@dateToUpdate INT = null,
	@reportingPeriodId INT,
	@COITypeId INT = 1
) 
  
AS  
  
BEGIN  
  

DECLARE @intHasOutsideActivities int
DECLARE @intHasEquityInterests int
DECLARE @intHasFamilyAssociations int
DECLARE @intHasRoyalties int

SET @intHasOutsideActivities =0
SET @intHasEquityInterests =0
SET @intHasFamilyAssociations =0
SET @intHasRoyalties=0



	IF @dateToUpdate = 1  
	BEGIN  
		SET @dateSubmitted = GETDATE()  
	END  
	ELSE IF @dateToUpdate = 2  
	BEGIN  
		SET @dateReviewed = GETDATE()  
	END  
	ELSE IF @dateToUpdate = 3  
	BEGIN  
		SET @lastNotified = GETDATE()  
	END  
	
	IF @id = 0  
	BEGIN  
		INSERT INTO ConflictOfInterest( AdmPersonId,  
			FwkDomainUserId,  
			LastNotified,  
			DateSubmitted,  
			DateReviewed,  
			HasOutsideActivities,  
			HasEquityInterests,  
			HasFamilyAssociations,
			Royalty,
			AggregateEquity,  
			PolicyViolation,  
			OtherActivity,  
			Status,  
			DocumentId,
			ReportingPeriodId,
			COITypeId
		)  
		VALUES  
		( 
			@admPersonId,  
			@fwkDomainUserId,  
			@lastNotified,  
			@dateSubmitted,  
			@dateReviewed,  
			@hasOutsideActivities,  
			@hasEquityInterests,  
			@hasFamilyAssociations,  
			@hasRoyalties,
			@aggregateEquity,  
			@policyViolation,  
			@otherActivity,  
			@status,  
			@documentId,
			@reportingPeriodId,
			@COITypeId
		)  
    
		IF @@ERROR <> 0 GOTO ERROR_HANDLER  
    
		SELECT IDENT_CURRENT('ConflictOfInterest')  
    
		RETURN   
	END  
	ELSE  
/* Need to update flags based on answers*/
	BEGIN  
    
		UPDATE ConflictOfInterest  
		SET  
			AdmPersonId = @admPersonId,  
			FwkDomainUserId = @fwkDomainUserId,  
			LastNotified = @lastNotified,  
			LastModified = GetDate(),  
			DateSubmitted = @dateSubmitted,  
			DateReviewed = @dateReviewed,  
			HasOutsideActivities = @hasOutsideActivities,  
			HasEquityInterests = @hasEquityInterests,  
			HasFamilyAssociations = @hasFamilyAssociations, 
			Royalty = @hasRoyalties, 
			AggregateEquity = @aggregateEquity,  
			PolicyViolation = @policyViolation,  
			OtherActivity = @otherActivity,  
			Status = @status,  
			DocumentId = @documentId,
			ReportingPeriodId = @reportingPeriodId,
			COITypeId = @COITypeId
		WHERE [Id] = @id  
    
		-- 
 /* Update Flags of ConflictOfInterest table  if all the answers are deleted*/
        -- Outside Activities
     
         SELECT  @intHasOutsideActivities = Count(QA.ID) 	    
		 FROM QuestionnaireAnswer QA       
		 INNER JOIN QuestionnaireVersion QV ON QV.Id = QA.QuestionnaireVersionId      
		 INNER JOIN Questionnaire Q ON Q.Id = QV.QuestionnaireId      
		 INNER JOIN COI_QuestionnaireAnswer_Map MAP ON MAP.QuestionnaireAnswerId = QA.Id   
		 INNER JOIN    ConflictOfInterest COI on COI.ID = MAP.COIID
		 WHERE MAP.COIId = @id
		 AND Q.[Name] = 'Your Outside Activities' 

               IF (@intHasOutsideActivities IS NULL OR @intHasOutsideActivities = 0 )
               BEGIN               
                
                   UPDATE  ConflictOfInterest 
                   SET  HasOutsideActivities = 0
                   WHERE Id = @id 
                   AND    HasOutsideActivities =1  
                                             
                END 

       -- Equity Interests
        
         SELECT  @intHasEquityInterests = Count(QA.ID) 	    
		 FROM QuestionnaireAnswer QA       
		 INNER JOIN QuestionnaireVersion QV ON QV.Id = QA.QuestionnaireVersionId      
		 INNER JOIN Questionnaire Q ON Q.Id = QV.QuestionnaireId      
		 INNER JOIN COI_QuestionnaireAnswer_Map MAP ON MAP.QuestionnaireAnswerId = QA.Id   
		 INNER JOIN    ConflictOfInterest COI on COI.ID = MAP.COIID
		 WHERE MAP.COIId = @id
		 AND Q.[Name] IN ('Your Equity Interests', 'Trustee Association') 

               IF (@intHasEquityInterests IS NULL OR @intHasEquityInterests = 0 )
                 BEGIN 
                   UPDATE  ConflictOfInterest 
                   SET  HasEquityInterests = 0
                   WHERE Id = @id  
				   AND   HasEquityInterests =1         
				 
                 END 
       

	 -- Family Associations
        
         SELECT  @intHasFamilyAssociations = Count(QA.ID) 	    
		 FROM QuestionnaireAnswer QA       
		 INNER JOIN QuestionnaireVersion QV ON QV.Id = QA.QuestionnaireVersionId      
		 INNER JOIN Questionnaire Q ON Q.Id = QV.QuestionnaireId      
		 INNER JOIN COI_QuestionnaireAnswer_Map MAP ON MAP.QuestionnaireAnswerId = QA.Id   
		 INNER JOIN    ConflictOfInterest COI on COI.ID = MAP.COIID
		 WHERE MAP.COIId = @id
		 AND Q.[Name] IN ('Your Family''s Associations and Equity Interests', 'Trustee''s Family Associations and Equity Interests') 

               IF (@intHasFamilyAssociations IS NULL OR @intHasFamilyAssociations = 0 )
                 BEGIN 
                   UPDATE  ConflictOfInterest 
                   SET  HasFamilyAssociations = 0
                   WHERE Id = @id             
					AND HasFamilyAssociations=1
                 END 

	 -- Royalties
        
         SELECT  @intHasRoyalties = Count(QA.ID) 	    
		 FROM QuestionnaireAnswer QA       
		 INNER JOIN QuestionnaireVersion QV ON QV.Id = QA.QuestionnaireVersionId      
		 INNER JOIN Questionnaire Q ON Q.Id = QV.QuestionnaireId      
		 INNER JOIN COI_QuestionnaireAnswer_Map MAP ON MAP.QuestionnaireAnswerId = QA.Id   
		 INNER JOIN    ConflictOfInterest COI on COI.ID = MAP.COIID
		 WHERE MAP.COIId = @id
		 AND Q.[Name] = 'Your Intellectual Property' 

               IF (@intHasRoyalties IS NULL OR @intHasRoyalties = 0 )
                 BEGIN 
                   UPDATE  ConflictOfInterest 
                   SET  Royalty = 0
                   WHERE Id = @id             
					AND Royalty =1
				
						
                 END 



		IF @@ERROR <> 0 GOTO ERROR_HANDLER  
  
		SELECT @id   
		RETURN  
    
	END  
  
	ERROR_HANDLER:  
		IF @@ERROR <> 0   
		BEGIN  
			RAISERROR('Unable to Save Conflict of Interest', 16, 1)  
			RETURN  
		END  
  
END
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

